package persistencia;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import negocio.Alumno;
import negocio.Cursada;

public class AlumnoDAO 
{
	private Connection c;
	private static AlumnoDAO instancia;
	
	private AlumnoDAO()
	{
		c = BDManager.getInstance().getConexion();
	}
	
	public static AlumnoDAO  getInstance()
	{
		if(instancia == null)
			instancia = new AlumnoDAO();
		return instancia;
	}
	
	public void guardar(Alumno alumno)
	{
		String query = String.format("INSERT INTO alumno(dni, nombre, apellido, telefono, mail, direccion, partido, provincia) "
				+ "VALUES ('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s',)",
				alumno.getDni(), alumno.getNombre(), alumno.getApellido(), alumno.getTelefono(), alumno.getTelefono(),
				alumno.getDireccion(), alumno.getPartido(), alumno.getProvincia());
		try {
			Statement s = c.createStatement();
			s.execute(query);
			s.close();
		} catch (SQLException e) 
		{
			e.printStackTrace();
		}
	}

	public ArrayList<Alumno> getAlumno(Cursada cursada)
	{
		String query = "SELECT * FROM alumno WHERE alumno.dni IN (SELECT alumno_fk FROM cursa WHERE cursada_fk=" + cursada.getId();
		ArrayList<Alumno> res = new ArrayList<Alumno>();
		try {
			Statement s = c.createStatement();
			ResultSet rs = s.executeQuery(query);
			while(rs.next())
			{
				res.add(new Alumno(rs.getString(1), rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5),rs.getString(6),rs.getString(7),rs.getString(8)));
			}
			s.close();
		} catch (SQLException e)
		{
			e.printStackTrace();
		}
		return res;
	}

	public Alumno getAlumno(int id)
	{
		String query = "SELECT *FROM alumno alumno WHERE dni =" + id;
		Alumno res = null;
		try
		{
			Statement s = c.createStatement();
			ResultSet rs = s.executeQuery(query);
			rs.first();
			res = new Alumno(rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6), rs.getString(7), rs.getString(8));
		}
		catch(SQLException e)
		{
			e.printStackTrace();
		}
		return res;
	}
}
